package org.example.manySheetExcel;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson.JSONObject;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.example.entity.UserDealerImportForm;
import org.example.handler.DealerImportVerifyHandler;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @author 望轩
 * @createDate 2025/8/21 10:37
 */
@RestController
@Slf4j
public class ExcelWithManySheetImportController {
    @PostMapping("/excelImportWithManySheet")
    public Object excelImport(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception {
        return importMultiSheetExcel(file);
    }

    /**
     * 导入多Sheet Excel文件
     * @param file 上传的Excel文件
     * @return 包含所有Sheet数据的Map（Key: Sheet名称, Value: RegionDTO列表）
     */
    public Map<String, List<RegionDTO>> importMultiSheetExcel(MultipartFile file) {
        Map<String, List<RegionDTO>> resultMap = new LinkedHashMap<>();
        // 这块错误信息是RegionDTO里面的validation校验注解识别的错误信息，比如字段为空而我们标注了@NotBlack注解
        //那么RegionDataListener监听器会自动识别出来这些错误信息；但是对于excel中填写的内容的逻辑问题，我们还需要
        //在写一套方法确认
        Map<String, List<String>> allSheetErrorMsgOfValidation = new HashMap<>();

        try {
            // 1. 获取Sheet列表（临时流）
            List<ReadSheet> sheets;
            //todo 正常情况下我们的stream流用过之后都是需要close关闭的，但是我们把流的获取写到了try后面的小括号里
            //todo 也就相当于是使用的try-with-resources，它会自动在后面加finally，然后自动调用流的close关闭方法
            //todo 正常情况下我们的InputStream tempStream = file.getInputStream()这句是放到try代码块里面的，
            //todo 然后还要写一个finally模块去关闭stream流，写上代码tempStream.close()，但是如果我们使用try-with-resources的方式
            //todo 可以直接把流的获取整体放到try后面的小括号内，这样try代码块里面的区域也可以获取这个流参数，并且不用我们先显示关闭流
            //todo try-with-resources会自动帮我们关闭流，你可以理解成它会自动加个finally模块，然后把流close
            try (InputStream tempStream = file.getInputStream()) {
                sheets = EasyExcel.read(tempStream).build().excelExecutor().sheetList();
            }

            // 2. 遍历Sheet
            for (ReadSheet sheet : sheets) {
                String sheetName = sheet.getSheetName();
                RegionDataListener listener = new RegionDataListener(); // 独立监听器

                // 3. 为当前Sheet创建新输入流
                try (InputStream sheetStream = file.getInputStream()) {
                    EasyExcel.read(sheetStream)
                            .excelType(ExcelTypeEnum.XLSX)
                            .head(RegionDTO.class)
                            .sheet(sheetName) // 或 .sheet(sheet.getSheetNo())
                            .registerReadListener(listener)
                            .doRead();
                } catch (Exception e) {
                    log.error("Sheet[{}]读取失败: {}", sheetName, e.getMessage());
                }

                // 4. 收集数据
                resultMap.put(sheetName, listener.getValidDataList());

                // 5. 处理错误数据
                if (!listener.getInvalidDataList().isEmpty()) {
                    Map<String, List<String>> curSheetErrorMsgOfValidation = handleInvalidData(sheetName, listener.getInvalidDataList());
                    allSheetErrorMsgOfValidation.putAll(curSheetErrorMsgOfValidation);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException("Excel处理失败: " + e.getMessage());
        }

        if (MapUtils.isNotEmpty(allSheetErrorMsgOfValidation)) {
            //这块实际业务中直接把错误字符串sheetErrorMsgOfValidation返回给前端，前端再进行展示
            throw new RuntimeException("Excel处理失败: " + allSheetErrorMsgOfValidation);
        }

        Map<String, List<String>> logicErrorMsg = handlerLogicError(resultMap);
        if (MapUtils.isNotEmpty(logicErrorMsg)) {
            throw new RuntimeException("Excel内容逻辑有误: " + logicErrorMsg);
        }

        //todo 如果excel表格中对应的RegionDTO实体中的所有的validation注解校验都通过了，并且逻辑上也没有错误了，那么紧接着要去看数据库
        //todo 里面是否已经有了数据行，如果有了就不导入了，没有则导入

        return resultMap;
    }

    private Map<String, List<String>> handlerLogicError(Map<String, List<RegionDTO>> resultMap) {
        Map<String, List<String>> allLogicErrorSheet = new HashMap<>();

        for (Map.Entry<String, List<RegionDTO>> entry : resultMap.entrySet()) {
            String sheetName = entry.getKey();
            List<String> allLogicErrorRowOfCurSheet = new ArrayList<>();

            List<RegionDTO> dataListCurSheet = entry.getValue();
            for (RegionDTO curRow : dataListCurSheet) {
                String error1 = "";
                String error2 = "";
                String error3 = "";
                String error4 = "";
                StringBuilder curRowLogicErrorMsg = new StringBuilder("第" + curRow.getRowNum() + "行异常，异常信息：");

                int level = curRow.getRegionLevel();
                if (level != 1 && level != 2) {
                    error1 = "区域层级格式错误，只能为1或者2；";
                }

                String type = curRow.getType();
                if (!StringUtils.equals(type, "AREA") && !StringUtils.equals(type, "COUNTRY")) {
                    error2 = "类型只能为AREA或者是COUNTRY；";
                }

                if ((level == 1 && !StringUtils.equals(type, "AREA")) || (level == 2 && !StringUtils.equals(type, "COUNTRY"))) {
                    error3 = "区域层级和类型不匹配；";
                }

                if (level == 2 && StringUtils.isEmpty(curRow.getCode())) {
                    error4 = "区域层级为2时，Code不能为空；";
                }

                if (StringUtils.isNotEmpty(error1) || StringUtils.isNotEmpty(error2)
                        || StringUtils.isNotEmpty(error3) || StringUtils.isNotEmpty(error4)) {

                    curRowLogicErrorMsg.append(error1).append(error2).append(error3).append(error4);
                    allLogicErrorRowOfCurSheet.add(curRowLogicErrorMsg.toString());
                }

            }

            if (!allLogicErrorRowOfCurSheet.isEmpty()) {
                allLogicErrorSheet.put("[" + sheetName + "]", allLogicErrorRowOfCurSheet);
            }
        }

        return allLogicErrorSheet;
    }

    /**
     * 处理无效数据（返回错误信息）
     * @param sheetName Sheet名称
     * @param invalidDataList 无效数据列表
     */
    private Map<String, List<String>> handleInvalidData(String sheetName, List<RegionDTO> invalidDataList) {
        Map<String, List<String>> curSheetErrorMsg = new HashMap<>();
        List<String> allRowErrorMsg = new ArrayList<>();

        for (RegionDTO invalidData : invalidDataList) {
            String curRowErrorMsg = "第" + (invalidData.getRowNum() + "行错误，错误信息：" + invalidData.getErrorMsg());
            allRowErrorMsg.add(curRowErrorMsg);
        }

        curSheetErrorMsg.put("[" + sheetName + "]", allRowErrorMsg);
        return curSheetErrorMsg;
    }

}

